package com.thesis.service.impl;

import com.thesis.dao.GreaterThanOneDao;
import com.thesis.dao.ThesisDao;
import com.thesis.entity.GreaterThanOne;
import com.thesis.entity.Thesis;
import com.thesis.response.Result;
import com.thesis.service.UploadService;
import com.thesis.vo.Vo;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;
import org.springframework.util.StringUtils;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import java.io.*;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;
import java.util.Objects;

@Slf4j
@Service
public class UploadServiceImpl implements UploadService {
    @Resource
    private ThesisDao thesisDao;
    @Resource
    private GreaterThanOneDao greaterThanOneDao;
    /**
     * excel上传
     * @param multipartFile
     * @return
     */
    @Override
    public Result upload(MultipartFile multipartFile) throws IOException {
        //原始文件名称
        String originalFilename = multipartFile.getOriginalFilename();
        assert originalFilename != null;
        //文件后缀
        String suffix = originalFilename.substring(originalFilename.lastIndexOf("."));
        if (".xls".equals(suffix) || ".xlsx".equals(suffix)) {
            InputStream inputStream = multipartFile.getInputStream();
            Workbook workbook = new XSSFWorkbook(inputStream);
            List<String[]> list = new ArrayList<>();
            for (int i = 0; i < workbook.getNumberOfSheets(); i++) {
                Sheet sheetAt = workbook.getSheetAt(i);
                if (sheetAt == null) {
                    continue;
                }
                //获取第一个有数据的开始行
                int firstRowNum = sheetAt.getFirstRowNum();
                //获取最后一个有数据的结束行
                int lastRowNum = sheetAt.getLastRowNum();
                //跳过标题行,
                for (int rowNum = firstRowNum + 1; rowNum <= lastRowNum; rowNum++) {
                    //获取当前行
                    Row row = sheetAt.getRow(rowNum);
                    if (row == null) {
                        continue;
                    }
                    //0
                    int firstCellNum = row.getFirstCellNum();
                    //143
                    int physicalNumberOfCells = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()-12];
                    //循环当前行
                    for (int cellNum = firstCellNum + 12; cellNum < physicalNumberOfCells; cellNum++) {
                        //从13个单元格开始获取（Q7_row1_column1开始获取），前面的不要了
                        Cell cell = row.getCell(cellNum);
                        //设置单元格类型
                        this.setExcelType(cell);
                        String stringCellValue = cell.getStringCellValue();
                        if("(空)".equals(stringCellValue)){
                            stringCellValue = "";
                        }
                        // 如果当前这个单元格里面的数字大于1，都都需要除以10
                        if(StringUtils.hasText(stringCellValue)){
                            double i1 = Double.parseDouble(stringCellValue);
                            double a = i1 / 10;
                            stringCellValue = String.valueOf(a);
                        }
                        //从0号数组开始赋值
                        cells[cellNum-12] = stringCellValue;
                    }
                    list.add(cells);
                }
            }
            workbook.close();
            List<Thesis> thesesList = new ArrayList<>();
            //开始把数据保存到数据库
            for (String[] strings : list) {
                Thesis thesis = new Thesis();

                thesis.setQ7Row1Column1(strings[0]);
                thesis.setQ7Row1Column2(strings[1]);
                thesis.setQ7Row1Column3(strings[2]);
                thesis.setQ7Row1Column4(strings[3]);
                thesis.setQ7Row1Column5(strings[4]);
                thesis.setQ7Row2Column1(strings[5]);
                thesis.setQ7Row2Column2(strings[6]);
                thesis.setQ7Row2Column3(strings[7]);
                thesis.setQ7Row2Column4(strings[8]);
                thesis.setQ7Row2Column5(strings[9]);

                thesis.setQ8Row1Column1(strings[10]);
                thesis.setQ8Row1Column2(strings[11]);
                thesis.setQ8Row1Column3(strings[12]);
                thesis.setQ8Row1Column4(strings[13]);
                thesis.setQ8Row1Column5(strings[14]);
                thesis.setQ8Row2Column1(strings[15]);
                thesis.setQ8Row2Column2(strings[16]);
                thesis.setQ8Row2Column3(strings[17]);
                thesis.setQ8Row2Column4(strings[18]);
                thesis.setQ8Row2Column5(strings[19]);

                thesis.setQ9Row1Column1(strings[20]);
                thesis.setQ9Row1Column2(strings[21]);
                thesis.setQ9Row1Column3(strings[22]);
                thesis.setQ9Row1Column4(strings[23]);
                thesis.setQ9Row1Column5(strings[24]);
                thesis.setQ9Row2Column1(strings[25]);
                thesis.setQ9Row2Column2(strings[26]);
                thesis.setQ9Row2Column3(strings[27]);
                thesis.setQ9Row2Column4(strings[28]);
                thesis.setQ9Row2Column5(strings[29]);

                thesis.setQ10Row1Column1(strings[30]);
                thesis.setQ10Row1Column2(strings[31]);
                thesis.setQ10Row1Column3(strings[32]);
                thesis.setQ10Row1Column4(strings[33]);
                thesis.setQ10Row1Column5(strings[34]);
                thesis.setQ10Row2Column1(strings[35]);
                thesis.setQ10Row2Column2(strings[36]);
                thesis.setQ10Row2Column3(strings[37]);
                thesis.setQ10Row2Column4(strings[38]);
                thesis.setQ10Row2Column5(strings[39]);

                thesis.setQ11Row1Column1(strings[40]);
                thesis.setQ11Row1Column2(strings[41]);
                thesis.setQ11Row1Column3(strings[42]);
                thesis.setQ11Row1Column4(strings[43]);
                thesis.setQ11Row1Column5(strings[44]);
                thesis.setQ11Row2Column1(strings[45]);
                thesis.setQ11Row2Column2(strings[46]);
                thesis.setQ11Row2Column3(strings[47]);
                thesis.setQ11Row2Column4(strings[48]);
                thesis.setQ11Row2Column5(strings[49]);

                thesis.setQ12Row1Column1(strings[50]);
                thesis.setQ12Row1Column2(strings[51]);
                thesis.setQ12Row1Column3(strings[52]);
                thesis.setQ12Row1Column4(strings[53]);
                thesis.setQ12Row1Column5(strings[54]);
                thesis.setQ12Row2Column1(strings[55]);
                thesis.setQ12Row2Column2(strings[56]);
                thesis.setQ12Row2Column3(strings[57]);
                thesis.setQ12Row2Column4(strings[58]);
                thesis.setQ12Row2Column5(strings[59]);

                thesis.setQ13Row1Column1(strings[60]);
                thesis.setQ13Row1Column2(strings[61]);
                thesis.setQ13Row1Column3(strings[62]);
                thesis.setQ13Row1Column4(strings[63]);
                thesis.setQ13Row1Column5(strings[64]);
                thesis.setQ13Row2Column1(strings[65]);
                thesis.setQ13Row2Column2(strings[66]);
                thesis.setQ13Row2Column3(strings[67]);
                thesis.setQ13Row2Column4(strings[68]);
                thesis.setQ13Row2Column5(strings[69]);

                thesis.setQ14Row1Column1(strings[70]);
                thesis.setQ14Row1Column2(strings[71]);
                thesis.setQ14Row1Column3(strings[72]);
                thesis.setQ14Row1Column4(strings[73]);
                thesis.setQ14Row1Column5(strings[74]);
                thesis.setQ14Row2Column1(strings[75]);
                thesis.setQ14Row2Column2(strings[76]);
                thesis.setQ14Row2Column3(strings[77]);
                thesis.setQ14Row2Column4(strings[78]);
                thesis.setQ14Row2Column5(strings[79]);

                thesis.setQ15Row1Column1(strings[80]);
                thesis.setQ15Row1Column2(strings[81]);
                thesis.setQ15Row1Column3(strings[82]);
                thesis.setQ15Row1Column4(strings[83]);
                thesis.setQ15Row1Column5(strings[84]);
                thesis.setQ15Row2Column1(strings[85]);
                thesis.setQ15Row2Column2(strings[86]);
                thesis.setQ15Row2Column3(strings[87]);
                thesis.setQ15Row2Column4(strings[88]);
                thesis.setQ15Row2Column5(strings[89]);

                thesis.setQ16Row1Column1(strings[90]);
                thesis.setQ16Row1Column2(strings[91]);
                thesis.setQ16Row1Column3(strings[92]);
                thesis.setQ16Row1Column4(strings[93]);
                thesis.setQ16Row1Column5(strings[94]);
                thesis.setQ16Row2Column1(strings[95]);
                thesis.setQ16Row2Column2(strings[96]);
                thesis.setQ16Row2Column3(strings[97]);
                thesis.setQ16Row2Column4(strings[98]);
                thesis.setQ16Row2Column5(strings[99]);

                thesis.setQ17Row1Column1(strings[100]);
                thesis.setQ17Row1Column2(strings[101]);
                thesis.setQ17Row1Column3(strings[102]);
                thesis.setQ17Row1Column4(strings[103]);
                thesis.setQ17Row1Column5(strings[104]);
                thesis.setQ17Row2Column1(strings[105]);
                thesis.setQ17Row2Column2(strings[106]);
                thesis.setQ17Row2Column3(strings[107]);
                thesis.setQ17Row2Column4(strings[108]);
                thesis.setQ17Row2Column5(strings[109]);

                thesis.setQ18Row1Column1(strings[110]);
                thesis.setQ18Row1Column2(strings[111]);
                thesis.setQ18Row1Column3(strings[112]);
                thesis.setQ18Row1Column4(strings[113]);
                thesis.setQ18Row1Column5(strings[114]);
                thesis.setQ18Row2Column1(strings[115]);
                thesis.setQ18Row2Column2(strings[116]);
                thesis.setQ18Row2Column3(strings[117]);
                thesis.setQ18Row2Column4(strings[118]);
                thesis.setQ18Row2Column5(strings[119]);

                thesis.setQ19Row1Column1(strings[120]);
                thesis.setQ19Row1Column2(strings[121]);
                thesis.setQ19Row1Column3(strings[122]);
                thesis.setQ19Row1Column4(strings[123]);
                thesis.setQ19Row1Column5(strings[124]);
                thesis.setQ19Row2Column1(strings[125]);
                thesis.setQ19Row2Column2(strings[126]);
                thesis.setQ19Row2Column3(strings[127]);
                thesis.setQ19Row2Column4(strings[128]);
                thesis.setQ19Row2Column5(strings[129]);

                thesis.setTotalValue(strings[130]);
                thesesList.add(thesis);
            }
            for (Thesis thesis : thesesList) {
                thesisDao.insert(thesis);
            }

            //大于1


            return Result.success(list);

        }
        return Result.error("文件格式错误");
    }

    @Override
    public Result insert() {
        List<Thesis> thesisList = thesisDao.queryAll();
        for (Thesis thesis : thesisList) {
            this.greaterThanOne("Q7","正问","Q7_row1_column1", "Q7_row1_column2", "Q7_row1_column3", "Q7_row1_column4", "Q7_row1_column5",thesis.getId());
            this.greaterThanOne("Q7","反问","Q7_row2_column1", "Q7_row2_column2", "Q7_row2_column3", "Q7_row2_column4", "Q7_row2_column5",thesis.getId());

            this.greaterThanOne("Q8","正问","Q8_row1_column1", "Q8_row1_column2", "Q8_row1_column3", "Q8_row1_column4", "Q8_row1_column5",thesis.getId());
            this.greaterThanOne("Q8","反问","Q8_row2_column1", "Q8_row2_column2", "Q8_row2_column3", "Q8_row2_column4", "Q8_row2_column5",thesis.getId());

            this.greaterThanOne("Q9","正问","Q9_row1_column1", "Q9_row1_column2", "Q9_row1_column3", "Q9_row1_column4", "Q9_row1_column5",thesis.getId());
            this.greaterThanOne("Q9","反问","Q9_row2_column1", "Q9_row2_column2", "Q9_row2_column3", "Q9_row2_column4", "Q9_row2_column5",thesis.getId());

            this.greaterThanOne("Q10","正问","Q10_row1_column1", "Q10_row1_column2", "Q10_row1_column3", "Q10_row1_column4", "Q10_row1_column5",thesis.getId());
            this.greaterThanOne("Q10","反问","Q10_row2_column1", "Q10_row2_column2", "Q10_row2_column3", "Q10_row2_column4", "Q10_row2_column5",thesis.getId());

            this.greaterThanOne("Q11","正问","Q11_row1_column1", "Q11_row1_column2", "Q11_row1_column3", "Q11_row1_column4", "Q11_row1_column5",thesis.getId());
            this.greaterThanOne("Q11","反问","Q11_row2_column1", "Q11_row2_column2", "Q11_row2_column3", "Q11_row2_column4", "Q11_row2_column5",thesis.getId());

            this.greaterThanOne("Q12","正问","Q12_row1_column1", "Q12_row1_column2", "Q12_row1_column3", "Q12_row1_column4", "Q12_row1_column5",thesis.getId());
            this.greaterThanOne("Q12","反问","Q12_row2_column1", "Q12_row2_column2", "Q12_row2_column3", "Q12_row2_column4", "Q12_row2_column5",thesis.getId());

            this.greaterThanOne("Q13","正问","Q13_row1_column1", "Q13_row1_column2", "Q13_row1_column3", "Q13_row1_column4", "Q13_row1_column5",thesis.getId());
            this.greaterThanOne("Q13","反问","Q13_row2_column1", "Q13_row2_column2", "Q13_row2_column3", "Q13_row2_column4", "Q13_row2_column5",thesis.getId());

            this.greaterThanOne("Q14","正问","Q14_row1_column1", "Q14_row1_column2", "Q14_row1_column3", "Q14_row1_column4", "Q14_row1_column5",thesis.getId());
            this.greaterThanOne("Q14","反问","Q14_row2_column1", "Q14_row2_column2", "Q14_row2_column3", "Q14_row2_column4", "Q14_row2_column5",thesis.getId());

            this.greaterThanOne("Q15","正问","Q15_row1_column1", "Q15_row1_column2", "Q15_row1_column3", "Q15_row1_column4", "Q15_row1_column5",thesis.getId());
            this.greaterThanOne("Q15","反问","Q15_row2_column1", "Q15_row2_column2", "Q15_row2_column3", "Q15_row2_column4", "Q15_row2_column5",thesis.getId());

            this.greaterThanOne("Q16","正问","Q16_row1_column1", "Q16_row1_column2", "Q16_row1_column3", "Q16_row1_column4", "Q16_row1_column5",thesis.getId());
            this.greaterThanOne("Q16","反问","Q16_row2_column1", "Q16_row2_column2", "Q16_row2_column3", "Q16_row2_column4", "Q16_row2_column5",thesis.getId());

            this.greaterThanOne("Q17","正问","Q17_row1_column1", "Q17_row1_column2", "Q17_row1_column3", "Q17_row1_column4", "Q17_row1_column5",thesis.getId());
            this.greaterThanOne("Q17","反问","Q17_row2_column1", "Q17_row2_column2", "Q17_row2_column3", "Q17_row2_column4", "Q17_row2_column5",thesis.getId());

            this.greaterThanOne("Q18","正问","Q18_row1_column1", "Q18_row1_column2", "Q18_row1_column3", "Q18_row1_column4", "Q18_row1_column5",thesis.getId());
            this.greaterThanOne("Q18","反问","Q18_row2_column1", "Q18_row2_column2", "Q18_row2_column3", "Q18_row2_column4", "Q18_row2_column5",thesis.getId());

            this.greaterThanOne("Q19","正问","Q19_row1_column1", "Q19_row1_column2", "Q19_row1_column3", "Q19_row1_column4", "Q19_row1_column5",thesis.getId());
            this.greaterThanOne("Q19","反问","Q19_row2_column1", "Q19_row2_column2", "Q19_row2_column3", "Q19_row2_column4", "Q19_row2_column5",thesis.getId());
        }
        return Result.success();
    }

    /**
     * 生成excel
     * @return
     */
    @Override
    public Result download() {
        List<GreaterThanOne> greaterThanOnes = greaterThanOneDao.queryAll();
        // 创建一个97年-2003.xls工作簿(一个工作簿中可以创建多个工作表sheet)
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //使用工作簿创建一个工作表
        HSSFSheet sheet = hssfWorkbook.createSheet("毕设论文");
        HSSFRow row = sheet.createRow(0);//获取第一行
        //设置excel表头
        String[] headers = new String[]{"序号","正问/反问","Q的类型","column1","column2","column3","column4","column5"};
        for (int i = 0; i < headers.length; i++) {
            HSSFCell cell = row.createCell(i);//当前行的第几列
            HSSFRichTextString text = new HSSFRichTextString(headers[i]);//获取头信息，这个对象会自动给你判空
            cell.setCellValue(text);//设置值
        }
        int num = 1;
        for (int i = 0; i < greaterThanOnes.size(); i++) {
            //创建当前行
            HSSFRow row1 = sheet.createRow(i + 1);//创建第一行(从第二行开始)
            //设置序号
            HSSFCell cell = row1.createCell(0);//当前行的第1列
            cell.setCellValue(num++);//设置序号
            //设置正问还是反问
            HSSFCell cell1 = row1.createCell(1);//当前行的第2列
            if(StringUtils.hasText(greaterThanOnes.get(i).getTypeEnum())){
                cell1.setCellValue(greaterThanOnes.get(i).getTypeEnum());
            }
            //Q的类型
            HSSFCell cell2 = row1.createCell(2);//当前行的第4列
            if(StringUtils.hasText(greaterThanOnes.get(i).getQNumber())){
                cell2.setCellValue(greaterThanOnes.get(i).getQNumber());
            }
            //colom1
            HSSFCell cell3 = row1.createCell(3);//当前行的第5列
            if(StringUtils.hasText(greaterThanOnes.get(i).getColumn1())){
                cell3.setCellValue(greaterThanOnes.get(i).getColumn1());
            }
            //colom2
            HSSFCell cell4 = row1.createCell(4);//当前行的第5列
            if(StringUtils.hasText(greaterThanOnes.get(i).getColumn2())){
                cell4.setCellValue(greaterThanOnes.get(i).getColumn2());
            }
            //colom1
            HSSFCell cell5 = row1.createCell(5);//当前行的第5列
            if(StringUtils.hasText(greaterThanOnes.get(i).getColumn3())){
                cell5.setCellValue(greaterThanOnes.get(i).getColumn3());
            }
            //colom1
            HSSFCell cell6 = row1.createCell(6);//当前行的第5列
            if(StringUtils.hasText(greaterThanOnes.get(i).getColumn4())){
                cell6.setCellValue(greaterThanOnes.get(i).getColumn4());
            }
            //colom1
            HSSFCell cell7 = row1.createCell(7);//当前行的第5列
            if(StringUtils.hasText(greaterThanOnes.get(i).getColumn5())){
                cell7.setCellValue(greaterThanOnes.get(i).getColumn5());
            }

        }
        //设置生成之后的名称
        String filename = "sxh-论文.xls";
        OutputStream outputStream = null;
        try {
            File exportDirFile = new File("./file/");
            log.info("exportDirFile={}",exportDirFile.getPath());
            if (!exportDirFile.exists()) {
                boolean mkdirs = exportDirFile.mkdirs();
                if(mkdirs){
                    log.info("创建文件夹{}成功",exportDirFile);
                }else {
                    log.error("创建文件夹失败");
                }
            }
            String fileUrl = exportDirFile.getPath() + File.separator + filename;
            log.info("fileUrl={}",fileUrl);
            outputStream = Files.newOutputStream(Paths.get(fileUrl));
            hssfWorkbook.write(outputStream);
            //将所有的windows \ 换成 / 以免Linux不支持
            return Result.success(fileUrl.replace("\\", "/"));
        } catch (Exception exception) {
            exception.printStackTrace();
            return Result.error("IO error");
        } finally {
            try {
                if (outputStream != null) {
                    outputStream.close();//关闭流
                }
                hssfWorkbook.close();//关闭流
            } catch (Exception exception) {
                exception.printStackTrace();
            }
        }
    }

    /**
     * 设置单元格格式，如果类型不为String的就设置为String，如果本来就为String则不设置
     * @param
     */
    public void setExcelType(Cell cell){
        if(cell.getCellType() != CellType.STRING){
            cell.setCellType(CellType.STRING);
        }
//        DataFormatter formatter = new DataFormatter();
//        formatter.formatCellValue(row.getCell(0));
//
//        DataFormatter formatter = new DataFormatter();
//        String formatCellValue = formatter.formatCellValue(cell);
    }

    /**
     *  *  "Q7_row1_column1", "Q7_row1_column2", "Q7_row1_column3", "Q7_row1_column4", "Q7_row1_column5"
     *      * @param qNumber  Q几？
     *      * @param typeEnum 正问/反问
     *      * @param col1 字段1 "Q7_row1_column1"
     *      * @param col2 字段2 "Q7_row1_column2",
     *      * @param col3 字段3 "Q7_row1_column3"
     *      * @param col4 字段4 "Q7_row1_column4"
     *      * @param col5 字段5 "Q7_row1_column5"
     *      id
     * @param id
     */
    public void greaterThanOne(String qNumber,
                               String typeEnum,
                               String col1,
                               String col2,
                               String col3,
                               String col4,
                               String col5,
                               Integer id){
        Vo vo = thesisDao.condtion(col1,col2,col3,col4,col5, id);
        String vcount = vo.getVcount();
        //如果不为空，而且五个数据相加等于1，那么保留
        if(StringUtils.hasText(vcount) && "1.0".equals(vo.getVcount()) || "1".equals(vo.getVcount())){
            String column1 = vo.getColumn1();
            String column2 = vo.getColumn2();
            String column3 = vo.getColumn3();
            String column4 = vo.getColumn4();
            String column5 = vo.getColumn5();
            GreaterThanOne greaterThanOne = new GreaterThanOne();
            greaterThanOne.setColumn1(column1);
            greaterThanOne.setColumn2(column2);
            greaterThanOne.setColumn3(column3);
            greaterThanOne.setColumn4(column4);
            greaterThanOne.setColumn5(column5);
            //设置q几
            greaterThanOne.setQNumber(qNumber);
            //设置正问还是反问
            greaterThanOne.setTypeEnum(typeEnum);
            int rows = greaterThanOneDao.insert(greaterThanOne);
            if(rows > 0){
                log.info("等于1的数据插入成功");
            }
        }
    }

}
